Enterprise Invoice Export

Introduction

Enterprise requires a compilation of all invoices that have been generated within a specified number of past days. The format and characteristics of the exported data are defined by Enterprise.

Purpose

Enterprise will import the generated Invoice file into their accounting system for further processing.

File format

Excel - *.xslx

Fields

Column Position Header Name Description Datatype Validation Max length Mandatory Default Value Example

A TEMPLATE ID Vendor name and date when the export file was created string A-Z, 0-9 and all special characters 100 Yes DigitalInnk_02/11/2022

B VENDOR NAME Supplier's group Name string A-Z, 0-9 and all special characters 50 Yes Grand Repair

C VENDOR ID Supplier Id in ViSN string A-Z, 0-9 and all special characters 10 Yes 0000006789

This is the supplier Id assigned to the supplier in ViSN. Refer to below image

This value is expected to be 10 characters in length. hence if the supplier id is less than 10 characters, adding a prefix with zero is performed so it will be consistent

Ex: GRAUTO shall become 0000GRAUTO

D VENDOR SET ID Vendor Id that should be mapped to Enterprise's account String A-Z characters 6 Yes APGBR APGBR

Enterprise needs a predefined identifier for their processing logic, data is maintained in Firebase for the same at

document: /leasecompany/[LeaseId]/config/exportsConfig 
attribute: vendorSetId

If a value exists, then it will be used as vendor-set-id for the export
If the document or the attribute doesn't exist, then the field will be left blank in the export

See Appendix 1 for the document sample

E VENDOR LOCATION Postcode/EirCode String A-Z & 0-9 20 Yes OX144SA

Enterprise needs a custom location for a few supplier groups, the data is maintained in Firebase for the same at

document: /leasecompany/[LeaseId]/config/exportsConfig 
attribute: vendorBillingEntityLocations

The postcode will be replaced based on the vendorBillingEntityLocations using the following methodology

Get Vendor billing locations that are pre-configured, and received in sets of account-id and postcode
If a value exists for the account to which the enquiry belongs, use the corresponding postcode for the export
If the value doesn't exist for the account, use the postcode of the supplier

See Appendix 1 for the document sample

F STATEMENT DATE Export generated date date nn/nn/nnnn in dd/MM/yyyy format 10 Yes 16/04/2023

G INVOICE NUMBER Invoice number string A-Z & 0-9 and '/' 20 Yes 23456/1

This will be the supplier invoice number with a slash and version number for each line or group of invoiced transaction.

Ex: Supplier Invoice Number NT/124881000
Line 1: NT/124881000/1
Line 2: NT/124881000/2
Line 3: NT/124881000/3

Refer to below image

H REPAIR DATE 4 days prior check‑in date date nn/nn/nnnn in dd/MM/yyyy format 10 Yes 16/04/2023

4 calendar days before the date the vehicle was checked in to the garage. Refer to below image

I VEHICLE DESCRIPTION Make Model of the vehicle string A-Z & 0-9, space, Dash, hyphen and underscore 150 No CITROEN C5 AIRCROSS

This is the make and model of the vehicle the enquiry has raised against. Refer to below image

J UNIT NUMBER BLANK

K LICENSE Vehicle Registration number string A-Z & 0-9 Dash, hyphen 12 Yes MH12JBS

This is the registration number of the vehicle the enquiry has raised against. Refer to below image

L VIN Chassis number of vehicle string A-Z & 0-9 17 No HXGKEAAMJ9K361461

This is the Chassis number of the vehicle the enquiry has raised against. Refer to below image

M CLAIM NUMBER Recharge Notes string A-Z and 0-9 & All Special Characters 150 No NXTORD001

Enterprise classifies the job lines in a jobSheet based on the claim number, these classifications are entered in the recharge notes against the jobLine

Classification is done as per below methodology

Non Recharge Line(s): The lines that don't have recharge enabled should be classified and grouped as 'Other'
Recharge Line(s) without claim number: The lines that have the recharge enabled, but no mention of recharge notes should be classified and grouped as 'Other' as well
Recharge Line(s) with claim number: The lines that have the recharge enabled, and a recharge note is added then they should be classified and grouped together based on the recharge note.
If there were more than one recharge notes added for a job sheet, there shall be multiple groups of lines to add in the export

N INVOICE TOTAL Invoice Total Cost number nnnn.nn 6 Yes 280.08

This is the total sum of the job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field (col-S + col-AA),

O ACCOUNT a custom value based on claim number string A-Z and 0-9 & All Special Characters 12 Y CLM001

Enterprise uses a static account for all the job lines that are classified as 'Other'

When claim number is 'Other' then populate with 1681 - 500300
When the claim number is not 'Other' then leave the field blank

P MILEAGE Job sheet mileage number 0-9 numbers 10 No 20120

Q DESCRIPTION TYPE BLANK

R DESCRIPTION BLANK

S DESCRIPTION AMOUNT number nnnn.nn 0-9 numbers 6 Yes 304.21

This is the sum of the pre-vat total of a job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field

T LABOR HOURS BLANK

U LABOR AMOUNT BLANK

V PARTS AMOUNT Net Invoice Cost number nnnn.nn 6 Yes 125.38

W MATERIAL AMOUNT BLANK

X TOWING BLANK

Y STORAGE BLANK

Z APPRAISAL/TEARDOWN BLANK

AA TAX Invoice VAT number nnnn.nn 6 No 94.82

This is the sum of the VAT amount of a job line or the group of job lines grouped based on the claim number that is entered in the recharge notes field

AB GPBR Branch string A-Z and 0-9 & All Special Characters 20 No BLANK U8T9

This is a custom value formulated based on the branch, repair date and branch history

branch history

A chronological trace of stock import where the modification reason is a change to the branch field of the respective stock

repair date

Column H of record being exported

branch

The branch is determined based on the location of the vehicle on a specific repair date, which is ascertained by examining the branchHistory array of the stock document. The repair must fall within the startDate and endDate parameters or be the most recent record without an end date.
If there is no match for a given date range, use blank value as branch.

click on this link, to know how GPBR is formulated

Data source

At the time of invoicing the enquiry the data is generated and kept ready for export

The generated data is maintained in Firebase at

doc: /accounts/[AccountId]/serviceUnits/[ServiceUnitId]/enquiries/[EnquiryId]/invoices/groupedByClaimNumber
attribute: records

Setup Required

A Scheduler should be created cloud scheduler with the following parameters

Name: GenerateEnterpriseExportXLS
Region: europe-west2
Frequency: 59 23 \* \* 7
TimeZone: GMT London
Topic: projects/biddirect-2/topics/generateEnterpriseExportXLS
Message body: a JSON object including the following parameters
- dateTimeStamp: in format yyyy-MMM-dd ex 2022-Sep-30 , when skipped it will default to current date
- leaseCompanyId: id that needs to be considered for enterprise lease
- forNoOfPreviousDays: No.of.days the export should go back from the date mentioned, when skipped defaults to 7 i.e., a week
- destinationFolder: folder which the export file must be placed in, it's optional

Appendix 1: export config document sample

{
  "vendorBillingEntityLocations": [
    {
      "accountId": "4eyYUszi3iYZzdw189rb",
      "locationCode": "OX144SA"
    },
    {
      "accountId": "ALRbwQqIVhr0wBjWu0Wp",
      "locationCode": "B980DA"
    }
  ],
  "vendorSetId": "APGBR"
}

Status: Accepted
Category: Protected
Authored By: Rama on May 10, 2023

Revisions
Dec, 2023 - Revise to include GPBR formulation based on branch code and locale, VN-10657
Nov, 2023 - Revise Repair Date column, VN-10291
Aug, 2023 - Revise to add claim number grouping, VN-7372
May, 2023 - Add Enterprise Export Documentation, VN-6517

Enterprise Invoice Export

Introduction​

Purpose​

File format​

Fields​

link

Data source​

Setup Required​

Appendix 1: export config document sample​